<?php
/**
 * Created by PhpStorm.
 * User: ZL
 * Date: 2016/5/12
 * Time: 10.37
 */
namespace ZL;

class Excel
{
    public function __construct()
    {
        $dir = dirname(__FILE__);
        require $dir."/PHPExcel/PHPExcel.php";
    }

    public function dumpExcel($title,$data,$name){
//        $dir = dirname(__FILE__);
//        require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel
        $excel = new \PHPExcel();
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T');
        for($i = 0;$i < count($title);$i++) {

            $excel->getActiveSheet()->setCellValue("$letter[$i]1","$title[$i]");

        }
        for ($i = 2;$i <= count($data) + 1;$i++) {

            $j = 0;

            foreach ($data[$i - 2] as $key=>$value) {

                $excel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");

                $j++;

            }

        }
        $aa = new \PHPExcel_IOFactory();
        $objWriter=$aa::createWriter($excel,'Excel5');//生成excel文件
        //$objWriter->save($dir."/export_1.xls");//保存文件
        $this->browser_export('Excel5',$name.'.xls');//输出到浏览器
        $objWriter->save("php://output");
    }

    //输出到浏览器
    public function browser_export($type,$filename){
        if($type=="Excel5"){
            header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
        }else{
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
        }
        header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称
        header('Cache-Control: max-age=0');//禁止缓存
    }
    //字符转换 数字转换大写字母
    public function keyGetTitleKey($k)
    {
        switch ($k){
            case 1:
                $v = A;
                break;
            case 2:
                $v = B;
                break;
            case 3:
                $v = C;
                break;
            case 4:
                $v = D;
                break;
            case 5:
                $v = E;
                break;
            case 6:
                $v = F;
                break;
            case 7:
                $v = G;
                break;
            case 8:
                $v = H;
                break;
            case 9:
                $v = I;
                break;
            case 10:
                $v = J;
                break;
            case 11:
                $v = K;
                break;
        }
        return $v;
    }


    //老胡写的读取相关方法
    protected function getExecl($filePath){
        $PHPReader = new \PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($filePath)) {
            $PHPReader = new \PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($filePath)) {
                echo 'no Excel';
                return;
            }
        }
        $excel = $PHPReader->load($filePath);
        return $excel;
    }

    public function getSheet($filePath,$sheetNo=0)
    {
        $excel = $this->getExecl($filePath);
        $sheet = $excel->getSheet($sheetNo);
        return $sheet;
    }

    public function getArr($filePath,$sheetNo=0,$startRow=1){
        $sheet = $this->getSheet($filePath,$sheetNo);
        $columnCount = $sheet->getHighestColumn();
        $rowCount = $sheet->getHighestRow();
        $arr = array();
        $keys = array();
        for($row=$startRow;$row<=$rowCount;$row++){
            for($column='A';$column<=$columnCount;$column++){
                $val = trim($sheet->getCellByColumnAndRow(ord($column)-65,$row)->getValue());
                if($row == $startRow){
                    $keys[$column] = $val;
                }else{
                    $data[$keys[$column]] = $val;
                }
            }
            if($row > $startRow){
                $arr[] = $data;
            }
        }
        return $arr;
    }
}